package com.nantian.office.excel;

import com.nantian.common.util.ClassUtils;
import com.nantian.common.util.CollectionUtils;
import com.nantian.common.util.StringUtils;
import com.nantian.exception.CellCheckException;
import com.nantian.exception.ExcelException;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <p>ClassName: ExcelUtils</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright©2014</p>
 * <p>Company: 广州南天电脑系统有限公司</p>
 * <p>Date: 2020-01-15</p>
 *
 * @author 刘晓辉
 * @version 1.0
 */
public class ExcelUtils {
    private static Logger log = LoggerFactory.getLogger(ExcelUtils.class);

    private static String XLSX = "xlsx";
    private static String XLS = "xls";
    private static String SUFFIX = "\\.";
    private static String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
    /**
     * 文件类型分割符
     */
    private static String FILE_TYPE_SEPARATOR = ".";
    /**
     * 科学记数法
     */
    private static String SCIENTIFIC_NOTATION = "E";


    /**
     * 将list中的map数据转成excel
     * @param out 输出流
     * @param template excel模版文件
     * @param excelType excel类型
     * @param startRowIndex 从第几行开始写，默认从0开始计算
     * @param data 数据
     * @param needTitle 是否需要添加标题列, 默认是不需要
     * @param sheetName sheet的标题
     * @param headers 标题，数据中map的key对应的中文
     * @param columnIndex 列的位置，该值和headers中的数据一一对应，表示header中的标题对应的列位置
     * @throws ExcelException 写入excel文件异常
     */
    public static void mapToExcelFile(OutputStream out,InputStream template, ExcelEnum excelType, int startRowIndex, List<Map<String, Object>> data, boolean needTitle, String sheetName, LinkedHashMap headers, Map<String, Integer> columnIndex) throws ExcelException {
        if (out == null) {
            throw new ExcelException("输出流不能为空");
        }
        if (CollectionUtils.isEmpty(data)) {
            throw new ExcelException("待写数据data不能为空");
        }
        //标题头
        Map<String, String> title = getDefaultHeaders(headers, data.get(0).keySet());
        //标题对应的列
        Map<String, Integer> colIndex = getDefaultColumnIndex(columnIndex, title);
        if (title.size() != colIndex.size()) {
            throw new ExcelException("标题数量和标题的位置数量不一致");
        }
        //创建工作薄
        Workbook workbook = createWorkBook(template, excelType);

        // 创建一个工作表sheet
        Sheet sheet = null;
        // 申明行
        Row row = null;
        // 申明单元格
        Cell cell = null;
        if (StringUtils.isBlank(sheetName)) {
            if (template != null) {
                sheet = workbook.getSheetAt(0);
            } else {
                sheet = workbook.createSheet();
            }
        } else {
            if (template != null) {
                sheet = workbook.getSheet(sheetName);
            } else {
                sheet = workbook.createSheet(sheetName);
            }
        }
        int start = startRowIndex;
        //需要新增标题
        if (needTitle) {
            //数据的开始位置为下一行
            start ++;
            row = sheet.createRow(startRowIndex);
            for (Map.Entry<String, String> entry : title.entrySet()) {
                //标题key
                String key = entry.getKey();
                //标题中文
                String value = entry.getValue();
                //cell的位置
                Integer ind = colIndex.get(key);
                cell = row.createCell(ind, CellType.STRING);
                cell.setCellValue(value);
            }
        }

        for (int j = 0; j < data.size(); j++) {
            int rowNum = j + start;
            row = sheet.createRow(rowNum);
            Map<String, Object> rowData = data.get(j);
            for (Map.Entry<String, String> entry : title.entrySet()) {
                //key
                String key = entry.getKey();
                //值
                Object value = rowData.get(key);
                //cell的位置
                Integer ind = colIndex.get(key);
                cell = row.createCell(ind, CellType.STRING);
                String val = "";
                if (value != null) {
                    val = String.valueOf(value);
                }
                cell.setCellValue(val);
            }
        }
        try {
            workbook.write(out);
            out.close();
        } catch (IOException e) {
            log.error(e.getMessage(), e);
            throw new ExcelException("写入文件io异常");
        }
    }

    /**
     * 将list中的javabean对象数据转成excel文档
     * @param out 输出流
     * @param template excel模版文件
     * @param excelType excel类型
     * @param startRowIndex 从第几行开始写，默认从0开始计算
     * @param data 数据
     * @param needTitle 是否需要添加标题列, 默认是不需要
     * @throws ExcelException excel异常
     */
    public static void objectToExcelFile(OutputStream out,InputStream template, ExcelEnum excelType, int startRowIndex, List<?> data, boolean needTitle) throws ExcelException {
        if (out == null) {
            throw new ExcelException("输出流不能为空");
        }
        if (CollectionUtils.isEmpty(data)) {
            throw new ExcelException("待写数据data不能为空");
        }
        Object obj = data.get(0);
        String sheetName = BeanUtils.getSheetName(obj.getClass());
        List<FieldProperty> fieldPropertys = BeanUtils.parsingField((Class<T>) obj.getClass());
        //列位置
        Map<String, Integer> columnIndex = new HashMap<>(16);
        //列标题
        LinkedHashMap<String, String> headers = new LinkedHashMap<>();
        for (FieldProperty fieldProperty : fieldPropertys) {
            //列位置
            columnIndex.put(fieldProperty.getFieldName(), fieldProperty.getIndex());
            //列中文标题
            String columnName = fieldProperty.getTitle();
            if (StringUtils.isBlank(columnName)) {
                //没有指定时，按字段名
                columnName = fieldProperty.getFieldName();
            }
            //列标题
            headers.put(fieldProperty.getFieldName(), columnName);
        }
        //获取数据的值
        List<Map<String, Object>> list = new ArrayList<>(data.size());
        for (Object object : data) {
            Map<String, Object> map = new HashMap<>(headers.size());
            for (FieldProperty fieldProperty : fieldPropertys) {
                //获取每一个字段的值
                Object value = BeanUtils.getMethodValue(object, fieldProperty);
                map.put(fieldProperty.getFieldName(), value);
            }
            list.add(map);
        }
        mapToExcelFile(out, template, excelType, startRowIndex, list, needTitle, sheetName, headers, columnIndex);
    }

    /**
     * 获取标题，如果没有指定标题时，将按数据中的key为标题
     * @param headers 标题头
     * @param keys 数据的key集合
     * @return 标题
     */
    private static Map<String, String> getDefaultHeaders(LinkedHashMap headers, Set<String> keys) {
        Map<String, String> titles = new LinkedHashMap<>();
        if (headers == null || headers.size() == 0) {
            for (String key : keys) {
                titles.put(key, key);
            }
        } else {
            titles = headers;
        }
        return titles;
    }

    private static Map<String, Integer> getDefaultColumnIndex(Map<String, Integer> columnIndex, Map<String, String> headers) {
        Map<String, Integer> col = new LinkedHashMap<>();
        if (columnIndex == null || columnIndex.size() == 0) {
            int index = 0;
            for (Map.Entry<String, String> entry : headers.entrySet()) {
                col.put(entry.getKey(), index);
                index ++;
            }

        } else {
            col = columnIndex;
        }
        return col;
    }


    /**
     * 解析excel文件
     * 支持xls、xlsx格式
     * @param filePath 文件路径
     * @param startRowIndex 从第几行开始读
     * @param maxRow 读取的最大行数
     * @param maxColumn 读取的最大列数
     * @return 转成list集合数据
     * @throws ExcelException excel异常
     */
    public static List<Map<String, Object>> parseObject(String filePath, int startRowIndex, int maxRow, int maxColumn) throws ExcelException {
        Workbook workbook = getWorkBook(filePath);
        //获取第一个sheet
        Sheet sheet = workbook.getSheetAt(0);
        //获取最大的行数
        int rownum = sheet.getPhysicalNumberOfRows();
        int rowCount = rownum - startRowIndex;
        if (rowCount > maxRow) {
            rowCount = maxRow;
        }
        List<Map<String, Object>> data = new ArrayList<>(rowCount);
        for (int i = startRowIndex; i < rownum; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            //获取最大列数
            int colnum = row.getLastCellNum();
            if (colnum > maxColumn) {
                colnum = maxColumn;
            }
            Map<String, Object> map = new HashMap<>(colnum);
            for (int j = 0; j < colnum; j++) {
                String value = getCellValue(row.getCell(j));
                map.put(String.valueOf(j), value);
            }
            data.add(map);
        }
        return data;
    }

    public static <T>List<T> parseObject(String filePath, int startRowIndex, Class<T> javaBean, int maxRow, int maxColumn) throws ExcelException {
        List<Map<String, Object>> data = parseObject(filePath, startRowIndex, maxRow, maxColumn);
        List<FieldProperty> fieldProperties = BeanUtils.parsingField(javaBean);
        if (CollectionUtils.isEmpty(fieldProperties)) {
            return null;
        }
        List<T> exdata = new ArrayList<>(data.size());
        for (int i = 0; i < data.size(); i++) {
            int row = i + startRowIndex;
            Map<String, Object> map = data.get(i);
            Object object = createJavaBean(javaBean, row);

            for (int j = 0; j < fieldProperties.size(); j++) {
                FieldProperty fieldProperty = fieldProperties.get(j);
                //获取列的值
                Object value = getFieldPropertyValue(map, fieldProperty, row, j);
                try {
                    Method method = javaBean.getMethod(fieldProperty.getFieldSetMethod(), fieldProperty.getFieldType());
                    method.invoke(object, value);
                } catch (NoSuchMethodException e) {
                    log.error(e.getMessage(), e);
                    CellCheckException cellCheckException = new CellCheckException("找不到方法[" + fieldProperty.getFieldSetMethod() + "]");
                    cellCheckException.setRowIndex(i);
                    cellCheckException.setCellIndex(j);
                    throw cellCheckException;
                } catch (IllegalAccessException e) {
                    log.error(e.getMessage(), e);
                    CellCheckException cellCheckException = new CellCheckException("调用方法失败[" + fieldProperty.getFieldSetMethod() + "]");
                    cellCheckException.setRowIndex(i);
                    cellCheckException.setCellIndex(j);
                    throw cellCheckException;
                } catch (InvocationTargetException e) {
                    log.error(e.getMessage(), e);
                    CellCheckException cellCheckException = new CellCheckException("调用方法失败[" + fieldProperty.getFieldSetMethod() + "]");
                    cellCheckException.setRowIndex(i);
                    cellCheckException.setCellIndex(j);
                    throw cellCheckException;
                }
            }
            exdata.add((T) object);
        }
        return exdata;
    }

    /**
     * 创建JAVA 对象
     * @param javaBean 类
     * @param row 行数
     * @return 对象
     */
    private static Object createJavaBean(Class javaBean, int row) {
        try {
            Object object = javaBean.newInstance();
            return object;
        } catch (InstantiationException e) {
            log.error(e.getMessage(), e);
            CellCheckException cellCheckException = new CellCheckException("初始化对象[" + javaBean.getName() + "]失败");
            cellCheckException.setRowIndex(row);
            throw cellCheckException;
        } catch (IllegalAccessException e) {
            log.error(e.getMessage(), e);
            CellCheckException cellCheckException = new CellCheckException("初始化对象[" + javaBean.getName() + "]失败");
            cellCheckException.setRowIndex(row);
            throw cellCheckException;
        }
    }

    private static Object getFieldPropertyValue(Map<String, Object> map, FieldProperty fieldProperty, int row, int j) {
        //获取列的值
        Object v = map.get(String.valueOf(fieldProperty.getIndex()));
        //判断是否能为空
        if (!fieldProperty.isEmpty()) {
            if (v == null || StringUtils.isBlank(v.toString())) {
                CellCheckException cellCheckException = new CellCheckException("数据项为空");
                cellCheckException.setRowIndex(row);
                cellCheckException.setCellIndex(j);
                throw cellCheckException;
            }
        }
        //判断正则
        if (StringUtils.isNotBlank(fieldProperty.getRegular())) {
            boolean check = v.toString().matches(fieldProperty.getRegular());
            if (!check) {
                CellCheckException cellCheckException = new CellCheckException("正则验证失败");
                cellCheckException.setRowIndex(row);
                cellCheckException.setCellIndex(j);
                throw cellCheckException;
            }
        }
        Object value = v;
        //日期格式不为空
        if (StringUtils.isNotBlank(fieldProperty.getDateFormate())) {
            if (StringUtils.isNotBlank(value == null? "":value.toString())) {
                try {
                    //将原先的数据转成date
                    SimpleDateFormat df = new SimpleDateFormat(DATE_FORMAT);
                    Date date = df.parse(value.toString());
                    //格式化成所需要的日期格式
                    SimpleDateFormat newdf = new SimpleDateFormat(fieldProperty.getDateFormate());
                    String newDate = newdf.format(date);
                    value = newDate;
                    //判断是否日期
                    if (ClassUtils.isDate(fieldProperty.getFieldType().getName())) {
                        value = newdf.parse(value.toString());
                    }
                } catch (ParseException e) {
                    CellCheckException cellCheckException = new CellCheckException("日期格式转换出错");
                    cellCheckException.setRowIndex(row);
                    cellCheckException.setCellIndex(j);
                    throw cellCheckException;
                }
            }
        } else {
            value = BeanUtils.conversionType(fieldProperty, v);
        }
        return value;
    }

    /**
     * 获取excel文件类型
     * @param filePath 文件路径
     * @return excel文件类型
     */
    public static ExcelEnum getExcelType(String filePath) {
        if (StringUtils.isBlank(filePath)) {
            throw new ExcelException("excel文件路径为空");
        }
        if (filePath.indexOf(FILE_TYPE_SEPARATOR) == -1) {
            throw new ExcelException("路径中中的文件不是excel类型文件");
        }
        //按.号切割，最后一个就是文件的类型
        String[] paths = filePath.split(SUFFIX);
        String type = paths[paths.length - 1];
        if (XLS.equals(type)) {
            return ExcelEnum.XLS;
        } else if (XLSX.equals(type)) {
            return ExcelEnum.XLSX;
        } else {
            throw new ExcelException("路径中中的文件不是excel类型文件");
        }
    }

    /**
     * 获取excel工作薄
     * 支持xls、xlsx格式
     * @param filePath 文件路径
     * @return 返回工作薄
     */
    private static Workbook getWorkBook(String filePath) {
        if (StringUtils.isBlank(filePath)) {
            throw new ExcelException("excel文件中径为空");
        }
        Workbook workbook = null;
        //获取文件的类型
        ExcelEnum excelEnum = getExcelType(filePath);
        InputStream is = null;
        try {
            is = new FileInputStream(new File(filePath));
            if (excelEnum == ExcelEnum.XLSX) {
                workbook = new XSSFWorkbook(is);
            } else if (excelEnum == ExcelEnum.XLS) {
                workbook = new HSSFWorkbook(is);
            }
            return workbook;
        } catch (FileNotFoundException e) {
            log.error(e.getMessage(), e);
            throw new ExcelException("找不到文件：" + filePath);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
            throw new ExcelException("读取文件异常：" + filePath);
        } finally {
            IOUtils.closeQuietly(is);
        }
    }

    private static Workbook createWorkBook(InputStream template, ExcelEnum excelType) throws ExcelException {
        //创建工作薄
        Workbook workbook = null;
        if (template == null) {
            workbook = new SXSSFWorkbook();
        } else if (excelType == ExcelEnum.XLSX){
            try {
                workbook = new XSSFWorkbook(template);
            } catch (IOException e) {
                log.error(e.getMessage(), e);
                throw new ExcelException(e.getMessage());
            }
        } else if (excelType == ExcelEnum.XLS) {
            try {
                workbook = new HSSFWorkbook(template);
            } catch (IOException e) {
                log.error(e.getMessage(), e);
                throw new ExcelException(e.getMessage());
            }
        }
        return workbook;
    }


    /**
     * 获取cell的值
     * @param cell 指定的cell
     * @return cell的值
     */
    private static String getCellValue (Cell cell) {
        String value = null;
        if (cell != null) {
            CellType cellType = cell.getCellTypeEnum();
            switch (cellType) {
                case _NONE:
                    value = cell.getRichStringCellValue().getString();
                    break;
                case BLANK:
                    value = cell.getRichStringCellValue().getString();
                    break;
                case STRING:
                    value = cell.getRichStringCellValue().getString();
                    break;
                case ERROR:
                    value = cell.getRichStringCellValue().getString();
                    break;
                case BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    value = cell.getCellFormula();
                    break;
                case NUMERIC:
                    //日期格式
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat df = new SimpleDateFormat(DATE_FORMAT);
                        value = df.format(date);
                    } else {
                        String cellValue = String.valueOf(cell.getNumericCellValue());
                        if (cellValue.indexOf(SCIENTIFIC_NOTATION) != -1) {
                            //科学记数法，将转成数字
                            DecimalFormat df = new DecimalFormat("0");
                            value = df.format(cell.getNumericCellValue());
                        } else {
                            //非科学记数法，采用字符串读取，不会自动加上小数点
                            cell.setCellType(CellType.STRING);
                            value = cell.getRichStringCellValue().getString();
                        }
                    }
                    break;
                default:
                    break;
            }
        }
        return value;
    }


}
